<?php

namespace Admin\Controller;

use Think\Model;
use Org\Util;

/**
 * Description of SpReportController
 *
 * @author Myron
 */
class SpReportController extends AdminController
{

    //演示报表
    public function demo($name = null, $shopid = null)
    {
        if (IS_POST) {
//            if (!isset($name)) {
//               
//            } else {
//                $this->error("name 不呢为空！");
//            }
//            $map['overallNum'] = array('like', '%' . $name . '%');
//            $datalist = $this->lists('sp_overalldaliy', $map);


            $sqlstr = "SELECT * FROM gr_sp_overalldaliy";
            $where = " where overallNum like '%" . $name . "%' and shopId=" . $shopid;
            $datalist = $this->listsJ($sqlstr, $where);


            //$shoplist = M('sp_shops')->select();

            $Model = new Model();
            $shoplist = $Model->query("select * from gr_sp_shops where status=1");


            $this->assign('shoplist', $shoplist);
            $this->assign('name', "擦擦擦擦");
            $this->assign('shopid', $shopid);
            $this->assign('_list', $datalist);
            $this->display();
        } else {
            //$datalist=M('sp_overalldaliy')->select();
            //dump($datalist);
            $datalist = $this->lists('sp_overalldaliy');

            $map['status'] = 1;
            // $shoplist = M('sp_shops')->where($map)->select();
            $Model = new Model();
            $shoplist = $Model->query("select * from gr_sp_shops");
            //dump($shoplist);
            int_to_string($datalist);


            for ($i = 0; $i < count($datalist); $i++) {

                $datalist[$i]['aftertotalAmount'] = $datalist[$i]['totalAmount'] + 20;
                //dump($datalist[$i]);

            }

            $this->assign('shoplist', $shoplist);
            $this->assign('_list', $datalist);
            //dump($datalist);
            $this->display();
        }
    }

    public function DemoExport($shopid = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/demo");
        }
        if ($shopid === null || $shopid === "") {
            $sql = "SELECT overallNum,shopId,totalAmount FROM gr_sp_overalldaliy";
        } else {
            $sql = "SELECT overallNum,shopId,totalAmount FROM gr_sp_overalldaliy where shopId=" . $shopid;

        }
        $Model = new Model();
        $data = $Model->query($sql);

        if ($data) {

            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_demo_excel";

            $headArr = array("序列号", "店铺ID", "总销售额");

            $title = "订单列表";
            $this->getExcel($filename, $headArr, $data, $title);
        } else {
            $this->error("没有可导出的数据！");
        }
    }

    public function ExportJXC($overallnum = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/overallDaliy");
        } else {
            $dataTable = $this->getJXCReport($overallnum);
            if ($dataTable) {

                //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                import("Org.Util.PHPExcel");
                import("Org.Util.PHPExcel.Writer.Excel5");
                import("Org.Util.PHPExcel.IOFactory.php");

                $filename = "$overallnum.进销存报表";

                $headArr = array("商品ID", "商品名称", "商品编号", "上期结存", "入库", "退订货", "门店线下销售", "线上配货", "退货", "线上退换货入库", "盘点", "报损", "报增", "使用", "差异");

                $title = "进销存报表";
                $this->getExcel($filename, $headArr, $dataTable, $title);
            } else {
                $this->error("没有可导出的数据！");
            }
        }
    }

    public function ExportSellCategory($overallnum = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/overallDaliy");
        } else {
            $dataTable = $this->getByCategory($overallnum);
            if ($dataTable) {
                for ($i = 0; $i < count($dataTable); $i++) {
                    unset($dataTable[$i]["gPid"]);
                }
                //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                import("Org.Util.PHPExcel");
                import("Org.Util.PHPExcel.Writer.Excel5");
                import("Org.Util.PHPExcel.IOFactory.php");

                $filename = "$overallnum.销售分类统计报表";

                $headArr = array("类型", "数量", "小计");

                $title = "销售分类统计报表";
                $this->getExcel($filename, $headArr, $dataTable, $title);
            } else {
                $this->error("没有可导出的数据！");
            }
        }
    }

    public function ExportPaywayCategory($overallnum = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/overallDaliy");
        } else {
            $dataTable = $this->getPaywayByCategory($overallnum);
            if ($dataTable) {
                //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                import("Org.Util.PHPExcel");
                import("Org.Util.PHPExcel.Writer.Excel5");
                import("Org.Util.PHPExcel.IOFactory.php");

                $filename = "$overallnum.销售分类统计报表";

                $headArr = array("支付方式", "金额");

                $title = "支付分类统计报表";
                $this->getExcel($filename, $headArr, $dataTable, $title);
            } else {
                $this->error("没有可导出的数据！");
            }
        }
    }

    public function overallDaliy()
    {
        $sql = "select id,overallNum,shopId,beginTime,endTime from gr_sp_overalldaliy where status=1 ORDER by id desc";
        //$Model = new Model();
        //$overallDaliyList = $Model->query($sql);
        $overallDaliyList = $this->listsJ($sql);
        $this->assign("_list", $overallDaliyList);
        $this->display("overalldaliy");
    }

    public function overallDaliyJXC($overallnum = null)
    {
        $dataTable = $this->getJXCReport($overallnum);
        $this->assign("_list", $dataTable);
        $this->assign("overallNum", $overallnum);

        $this->display("overalldaliyjxc");

    }

    private function getJXCReport($overallnum)
    {
        $Model = new Model();
        $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            $beginTime = $temp[0]["beginTime"];
            $endTime = $temp[0]["endTime"];
            $shopId = $temp[0]["shopId"];

            $endTimeDate = date('Y-m-d', $endTime);
            $sql = "select endTime from gr_sp_overalldaliy where overallNum<'$overallnum' and shopId=$shopId order by id desc limit 1";
            $tempSub = $Model->query($sql);
            $endTimeDateSub = $endTime;
            if ($tempSub != null && count($tempSub) > 0) {
                $endTimeDateSub = $tempSub[0]["endTime"];
                $endTimeDateSub = date('Y-m-d', $endTimeDateSub);
            }
            $checkDateFlagMax = $endTimeDate;
            $checkDateFlagMaxSub = $endTimeDateSub;
            $noCheckCategoryIdStr = '33,34,35,42,43,46,49';

            //上期结存
            $sql = "select c.productId,p.name,p.code,c.realStock as changeCounts from gr_sp_checkstock c inner join  gr_sp_product p on c.productId=p.id where c.shopId=$shopId and c.checkDateFlag='$checkDateFlagMaxSub' and p.pCategoryId not in ($noCheckCategoryIdStr)";
            $kuCun = $Model->query($sql);

            //进货（即收货）
            $sqlJ = "select psc.productId,p.name,p.code,sum(changeCount) as changeCounts from gr_sp_pstockchangedetail psc inner join gr_sp_product p on psc.productId=p.id where psc.shopId=$shopId and psc.type=2 and psc.createat>$beginTime and psc.createat<$endTime and psc.status=1  and p.pCategoryId not in ($noCheckCategoryIdStr) group by psc.productId,p.name,p.code order by p.code";
            $sqlJ_Tui = "select psc.productId,p.name,p.code,sum(changeCount) as changeCounts from gr_sp_pstockchangedetail psc inner join gr_sp_product p on psc.productId=p.id where psc.shopId=$shopId and psc.type=8 and psc.createat>$beginTime and psc.createat<$endTime and psc.status=1  and p.pCategoryId not in ($noCheckCategoryIdStr) group by psc.productId,p.name,p.code order by p.code";
            $sqlX_shopSell = "select op.productId,p.name,p.code,sum(op.count) as changeCounts from (gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id where o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and (o.type=1 or o.type=5) and o.status=1 and p.pCategoryId not in ($noCheckCategoryIdStr) group by op.productId,p.name,p.code order by p.code";
            $sqlX_OnlineSell = "select op.productId,p.name,p.code,sum(op.count) as changeCounts from (gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id where o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=9 and o.status=1 and p.pCategoryId not in ($noCheckCategoryIdStr) group by op.productId,p.name,p.code order by p.code";
            $sqlX_Tui = "select op.productId,p.name,p.code,sum(op.count) as changeCounts from (gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id where o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=3 and o.status=1 and p.pCategoryId not in ($noCheckCategoryIdStr) group by op.productId,p.name,p.code order by p.code";
            $sqlX_OnlineTui = "select psc.productId,p.name,p.code,sum(psc.changeCount) as changeCounts from gr_sp_pstockchangedetail psc inner join gr_sp_product p on psc.productId=p.id where psc.type=7 and psc.createat>$beginTime and psc.createat<$endTime and psc.shopId=$shopId and p.pCategoryId not in ($noCheckCategoryIdStr) group by psc.productId,p.name,p.code";
            $sqlC = "select c.productId,p.name,p.code,c.realStock as changeCounts,c.minus,c.plus,c.used,c.diffeValue from gr_sp_checkstock c inner join  gr_sp_product p on c.productId=p.id where c.shopId=$shopId and c.checkDateFlag='$checkDateFlagMax' and p.pCategoryId not in ($noCheckCategoryIdStr)";

            $dtJ = $Model->query($sqlJ);
            $dtJ_Tui = $Model->query($sqlJ_Tui);
            $dtX_shopSell = $Model->query($sqlX_shopSell);
            $dtX_OnlineSell = $Model->query($sqlX_OnlineSell);
            $dtX_Tui = $Model->query($sqlX_Tui);
            $dtX_OnlineTui = $Model->query($sqlX_OnlineTui);
            $dtC = $Model->query($sqlC);

            $dataTable = [];
            $dataTableItem = ["", "", "", "", "", "", "", "", "", "", "", "", "", "", ""];
            for ($i = 0; $i < count($kuCun); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $kuCun[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][3] = $kuCun[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$kuCun[$i]["productId"], $kuCun[$i]["name"], ' ' . $kuCun[$i]["code"], $kuCun[$i]["changeCounts"], "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtJ); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtJ[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][4] = $dtJ[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtJ[$i]["productId"], $dtJ[$i]["name"], ' ' . $dtJ[$i]["code"], "0", $dtJ[$i]["changeCounts"], "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtJ_Tui); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtJ_Tui[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][5] = $dtJ_Tui[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtJ_Tui[$i]["productId"], $dtJ_Tui[$i]["name"], ' ' . $dtJ_Tui[$i]["code"], "0", "0", $dtJ_Tui[$i]["changeCounts"], "0", "0", "0", "0", "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtX_shopSell); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtX_shopSell[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][6] = $dtX_shopSell[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtX_shopSell[$i]["productId"], $dtX_shopSell[$i]["name"], ' ' . $dtX_shopSell[$i]["code"], "0", "0", "0", $dtX_shopSell[$i]["changeCounts"], "0", "0", "0", "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtX_OnlineSell); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtX_OnlineSell[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][7] = $dtX_OnlineSell[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtX_OnlineSell[$i]["productId"], $dtX_OnlineSell[$i]["name"], ' ' . $dtX_OnlineSell[$i]["code"], "0", "0", "0", "0", $dtX_OnlineSell[$i]["changeCounts"], "0", "0", "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtX_Tui); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtX_Tui[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][8] = $dtX_Tui[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtX_Tui[$i]["productId"], $dtX_Tui[$i]["name"], ' ' . $dtX_Tui[$i]["code"], "0", "0", "0", "0", "0", $dtX_Tui[$i]["changeCounts"], "0", "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtX_OnlineTui); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtX_OnlineTui[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][9] = $dtX_OnlineTui[$i]["changeCounts"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtX_OnlineTui[$i]["productId"], $dtX_OnlineTui[$i]["name"], ' ' . $dtX_OnlineTui[$i]["code"], "0", "0", "0", "0", "0", "0", $dtX_OnlineTui[$i]["changeCounts"], "0", "0", "0", "0", "0"];
                    $dataTable[] = $dataTableItem;
                }
            }
            for ($i = 0; $i < count($dtC); $i++) {
                $flag = false;
                for ($j = 0; $j < count($dataTable); $j++) {
                    if ($dataTable[$j][0] == $dtC[$i]["productId"]) {
                        $flag = true;
                        $dataTable[$j][10] = $dtC[$i]["changeCounts"];
                        $dataTable[$j][11] = $dtC[$i]["minus"];
                        $dataTable[$j][12] = $dtC[$i]["plus"];
                        $dataTable[$j][13] = $dtC[$i]["used"];
                        $dataTable[$j][14] = $dtC[$i]["diffeValue"];
                    }
                }
                if ($flag == false) {
                    $dataTableItem = [$dtC[$i]["productId"], $dtC[$i]["name"], ' ' . $dtC[$i]["code"], "0", "0", "0", "0", "0", "0", "0", $dtC[$i]["changeCounts"], $dtC[$i]["minus"], $dtC[$i]["plus"], $dtC[$i]["used"], $dtC[$i]["diffeValue"]];
                    $dataTable[] = $dataTableItem;
                }
            }
            return $dataTable;
        } else {
            return [];
        }
    }

    public function overallDaliyByCatgory($overallnum = null)
    {
        $dtRes = $this->getByCategory($overallnum);
        $this->assign("_list", $dtRes);
        $this->assign("overallNum", $overallnum);

        $this->display("overalldaliybycatgory");

    }

    private function  getByCategory($overallnum)
    {
        $Model = new Model();
        $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            $beginTime = $temp[0]["beginTime"];
            $endTime = $temp[0]["endTime"];
            $shopId = $temp[0]["shopId"];

            $sqlOffLine = "select '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=1 and o.status=1 group by pc.pid";
            $sqlGroup = "select '' as 类型, pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=5 and o.status=1 group by pc.pid";
            $sqlOnline = "select '' as 类型, pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=9 and o.status=1 group by pc.pid";
            $sqlOffLine_Tui = "select '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=3 and o.status=1 group by pc.pid";
            $sqlOnline_Tui = "select '' as 类型, pc.pid as gPid,sum(psc.changeCount) as gCount,-1 as gPrice from (gr_sp_pstockchangedetail psc inner join gr_sp_product p on psc.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and psc.createat>$beginTime and psc.createat<$endTime and psc.shopId=$shopId and psc.type=7 and psc.status=1 group by pc.pid";

            $dtOffLine = $Model->query($sqlOffLine);
            $dtGroup = $Model->query($sqlGroup);
            $dtOnline = $Model->query($sqlOnline);
            $dtOffLine_Tui = $Model->query($sqlOffLine_Tui);
            $dtOnline_Tui = $Model->query($sqlOnline_Tui);

            $dtRes = [];

            for ($i = 0; $i < count($dtOffLine); $i++) {
                if ($dtOffLine[$i]["gPid"] == 1) {
                    $dtOffLine[$i]["类型"] = "门店直销果汁";
                } else if ($dtOffLine[$i]["gPid"] == 2) {
                    $dtOffLine[$i]["类型"] = "门店直销水果";
                } else if ($dtOffLine[$i]["gPid"] == 3) {
                    $dtOffLine[$i]["类型"] = "门店直销小吃";
                } else if ($dtOffLine[$i]["gPid"] == 4) {
                    $dtOffLine[$i]["类型"] = "门店直销套餐";
                } else if ($dtOffLine[$i]["gPid"] == 5) {
                    $dtOffLine[$i]["类型"] = "门店直销处理品分类";
                } else if ($dtOffLine[$i]["gPid"] == 6) {
                    $dtOffLine[$i]["类型"] = "门店直销耗材分类";
                } else if ($dtOffLine[$i]["gPid"] == 7) {
                    $dtOffLine[$i]["类型"] = "门店直销服务费分类";
                }
                $dtRes[] = $dtOffLine[$i];
            }
            for ($i = 0; $i < count($dtGroup); $i++) {
                if ($dtGroup[$i]["gPid"] == 1) {
                    $dtGroup[$i]["类型"] = "团购果汁";
                } else if ($dtGroup[$i]["gPid"] == 2) {
                    $dtGroup[$i]["类型"] = "团购水果";
                } else if ($dtGroup[$i]["gPid"] == 3) {
                    $dtGroup[$i]["类型"] = "团购小吃";
                } else if ($dtGroup[$i]["gPid"] == 4) {
                    $dtGroup[$i]["类型"] = "团购套餐";
                } else if ($dtGroup[$i]["gPid"] == 5) {
                    $dtGroup[$i]["类型"] = "团购处理品分类";
                } else if ($dtGroup[$i]["gPid"] == 6) {
                    $dtGroup[$i]["类型"] = "团购耗材分类";
                } else if ($dtGroup[$i]["gPid"] == 7) {
                    $dtGroup[$i]["类型"] = "团购服务费分类";
                }
                $dtRes[] = $dtGroup[$i];
            }
            for ($i = 0; $i < count($dtOnline); $i++) {
                if ($dtOnline[$i]["gPid"] == 1) {
                    $dtOnline[$i]["类型"] = "线上果汁";
                } else if ($dtOnline[$i]["gPid"] == 2) {
                    $dtOnline[$i]["类型"] = "线上水果";
                } else if ($dtOnline[$i]["gPid"] == 3) {
                    $dtOnline[$i]["类型"] = "线上小吃";
                } else if ($dtOnline[$i]["gPid"] == 4) {
                    $dtOnline[$i]["类型"] = "线上套餐";
                } else if ($dtOnline[$i]["gPid"] == 5) {
                    $dtOnline[$i]["类型"] = "线上处理品分类";
                } else if ($dtOnline[$i]["gPid"] == 6) {
                    $dtOnline[$i]["类型"] = "线上耗材分类";
                } else if ($dtOnline[$i]["gPid"] == 7) {
                    $dtOnline[$i]["类型"] = "线上服务费分类";
                }
                $dtRes[] = $dtOnline[$i];
            }
            for ($i = 0; $i < count($dtOffLine_Tui); $i++) {
                if ($dtOffLine_Tui[$i]["gPid"] == 1) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货果汁";
                } else if ($dtOffLine_Tui[$i]["gPid"] == 2) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货水果";
                } else if ($dtOffLine_Tui[$i]["gPid"] == 3) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货小吃";
                } else if ($dtOffLine_Tui[$i]["gPid"] == 4) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货套餐";
                } else if ($dtOffLine_Tui[$i]["gPid"] == 5) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货处理品分类";
                } else if ($dtOffLine_Tui[$i]["gPid"] == 6) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货耗材分类";
                } else if ($dtOffLine_Tui[$i]["gPid"] == 7) {
                    $dtOffLine_Tui[$i]["类型"] = "门店退货服务费分类";
                }
                $dtRes[] = $dtOffLine_Tui[$i];
            }
            for ($i = 0; $i < count($dtOnline_Tui); $i++) {
                if ($dtOnline_Tui[$i]["gPid"] == 1) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货果汁";
                } else if ($dtOnline_Tui[$i]["gPid"] == 2) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货水果";
                } else if ($dtOnline_Tui[$i]["gPid"] == 3) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货小吃";
                } else if ($dtOnline_Tui[$i]["gPid"] == 4) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货套餐";
                } else if ($dtOnline_Tui[$i]["gPid"] == 5) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货处理品分类";
                } else if ($dtOnline_Tui[$i]["gPid"] == 6) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货耗材分类";
                } else if ($dtOnline_Tui[$i]["gPid"] == 7) {
                    $dtOnline_Tui[$i]["类型"] = "线上退货服务费分类";
                }
                $dtRes[] = $dtOnline_Tui[$i];
            }
            return $dtRes;
        } else {
            return [];
        }
    }

    public function overallDaliyGetPayDetail($overallnum = null)
    {
        $dt_Payway = $this->getPaywayByCategory($overallnum);
        $this->assign("_list", $dt_Payway);
        $this->assign("overallNum", $overallnum);

        $this->display("overalldaliygetpaydetail");

    }

    private function  getPaywayByCategory($overallnum)
    {
        $Model = new Model();
        $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            $beginTime = $temp[0]["beginTime"];
            $endTime = $temp[0]["endTime"];
            $shopId = $temp[0]["shopId"];

            $dt_Payway = $this->calculatePaywayByCategory($beginTime, $endTime, $shopId);
            return $dt_Payway;
        } else {
            return [];
        }
    }

    private function  calculatePaywayByCategory($beginTime, $endTime, $shopId)
    {
        $Model = new Model();

        $minOrderNum = "";
        $maxOrderNum = "";
        $sqlOrderMinMax = "select min(orderNum) as minOrderNum,max(orderNum) as maxOrderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (type=1 or type=3 or type=5)";
        $dtOrderMinMax = $Model->query($sqlOrderMinMax);
        if ($dtOrderMinMax != null && count($dtOrderMinMax) > 0) {
            $minOrderNum = $dtOrderMinMax[0]["minOrderNum"];
            $maxOrderNum = $dtOrderMinMax[0]["maxOrderNum"];
        }

        //$sqlTotalSellDetail_payway1 = "select pw.name as 支付方式, sum(p.firstPayTypeAmount)as 金额 from  gr_sp_order o inner join (gr_sp_grouppay p  inner join gr_sp_payway pw on p.firstPayTypeID=pw.id) on o.orderNum=p.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (o.type=1 or o.type=5) and o.status=1 group by p.firstPayTypeID,pw.name";
        $sqlTotalSellDetail_payway1 = "select ttt.name as 支付方式, sum(ttt.firstPayTypeAmount)as 金额 from ( select tt.*,pw.name from (select t.*,p.firstPayTypeID,p.firstPayTypeAmount from ( select * from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime  and (o.type=1 or o.type=5) and o.status=1 ) t  inner join ( SELECT * from  gr_sp_grouppay where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') p on t.orderNum=p.orderNum) tt inner join gr_sp_payway pw on tt.firstPayTypeID=pw.id) ttt group by ttt.firstPayTypeID,ttt.name";
        //$sqlTotalSellDetail_payway2 = "select pw.name as 支付方式, sum(p.secondPayTypeAmount)as 金额 from gr_sp_order o inner join (gr_sp_grouppay p  inner join gr_sp_payway pw on p.secondPayTypeId=pw.id) on o.orderNum=p.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (o.type=1 or o.type=5) and o.status=1 group by p.secondPayTypeId,pw.name";
        $sqlTotalSellDetail_payway2 = "select ttt.name as 支付方式, sum(ttt.secondPayTypeAmount)as 金额 from ( select tt.*,pw.name from (select t.*,p.secondPayTypeId,p.secondPayTypeAmount from ( select * from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime  and (o.type=1 or o.type=5) and o.status=1 ) t  inner join ( SELECT * from  gr_sp_grouppay where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') p on t.orderNum=p.orderNum) tt inner join gr_sp_payway pw on tt.secondPayTypeID=pw.id) ttt group by ttt.secondPayTypeID,ttt.name";

//        $sqlTotalSellDetail_payway1 = "select '现金' as 支付方式, 0 as 金额 from gr_sp_order o inner join gr_sp_grouppay p on o.orderNum=p.orderNum   where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (o.type=1 or o.type=5) and o.status=1 ";
//        $sqlTotalSellDetail_payway2 = "select pw.name as 支付方式, 0 as 金额 from gr_sp_order o inner join gr_sp_grouppay p on o.orderNum=p.orderNum inner join gr_sp_payway pw on p.secondPayTypeId=pw.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (o.type=1 or o.type=5) and o.status=1 ";

        $dt_Payway1 = $Model->query($sqlTotalSellDetail_payway1);
        $dt_Payway2 = $Model->query($sqlTotalSellDetail_payway2);
        //合并两种支付方式
        $dt_Payway = [];
        for ($i = 0; $i < count($dt_Payway1); $i++) {
            $dt_Payway[] = $dt_Payway1[$i];
        }
        for ($j = 0; $j < count($dt_Payway2); $j++) {
            $flag = false;
            for ($k = 0; $k < count($dt_Payway); $k++) {
                if ($dt_Payway[$k]["支付方式"] == $dt_Payway2[$j]["支付方式"]) {
                    $flag = true;
                    $dt_Payway[$k]["金额"] = round(floatval($dt_Payway[$k]["金额"]) + floatval($dt_Payway2[$j]["金额"]), 2);
                }
            }
            if ($flag == false) {
                $dt_Payway[] = $dt_Payway2[$j];
            }
        }
        //取出退款
//        $sqlTotalSellDetail_payway1_tui = "select pw.name as 退款方式, sum(firstPayTypeAmount)as 金额 from gr_sp_order o inner join (gr_sp_grouppay p  inner join gr_sp_payway pw on p.firstPayTypeID=pw.id) on o.orderNum=p.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and o.type=3 and o.status=1 group by p.firstPayTypeID,pw.name";
        $sqlTotalSellDetail_payway1_tui = "select ttt.name as 退款方式, sum(ttt.firstPayTypeAmount)as 金额 from ( select tt.*,pw.name from (select t.*,p.firstPayTypeID,p.firstPayTypeAmount from ( select * from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime  and o.type=3 and o.status=1 ) t  inner join ( SELECT * from  gr_sp_grouppay where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') p on t.orderNum=p.orderNum) tt inner join gr_sp_payway pw on tt.firstPayTypeID=pw.id) ttt group by ttt.firstPayTypeID,ttt.name";
//        $sqlTotalSellDetail_payway2_tui = "select pw.name as 退款方式, sum(secondPayTypeAmount)as 金额 from gr_sp_order o inner join (gr_sp_grouppay p  inner join gr_sp_payway pw on p.secondPayTypeId=pw.id) on o.orderNum=p.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and o.type=3 and o.status=1 group by p.secondPayTypeId,pw.name";
        $sqlTotalSellDetail_payway2_tui = "select ttt.name as 退款方式, sum(ttt.secondPayTypeAmount)as 金额 from ( select tt.*,pw.name from (select t.*,p.secondPayTypeId,p.secondPayTypeAmount from ( select * from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime  and o.type=3 and o.status=1 ) t  inner join ( SELECT * from  gr_sp_grouppay where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') p on t.orderNum=p.orderNum) tt inner join gr_sp_payway pw on tt.secondPayTypeID=pw.id) ttt group by ttt.secondPayTypeID,ttt.name";
        $dt_Payway1_tui = $Model->query($sqlTotalSellDetail_payway1_tui);
        $dt_Payway2_tui = $Model->query($sqlTotalSellDetail_payway2_tui);

        //合并两种退款支付方式
        $dt_Payway_tui = [];
        for ($i = 0; $i < count($dt_Payway1_tui); $i++) {
            $dt_Payway_tui[] = $dt_Payway1_tui[$i];
        }
        for ($j = 0; $j < count($dt_Payway2_tui); $j++) {
            $flag = false;
            for ($k = 0; $k < count($dt_Payway_tui); $k++) {
                if ($dt_Payway_tui[$k]["退款方式"] == $dt_Payway2_tui[$j]["退款方式"]) {
                    $flag = true;
                    $dt_Payway_tui[$k]["金额"] = round(floatval($dt_Payway_tui[$k]["金额"]) + floatval($dt_Payway2_tui[$j]["金额"]), 2);
                }
            }
            if ($flag == false) {
                $dt_Payway_tui[] = $dt_Payway2_tui[$j];
            }
        }

        //支付金额减去退款金额得到当日实收金额
        for ($i = 0; $i < count($dt_Payway); $i++) {
            for ($j = 0; $j < count($dt_Payway_tui); $j++) {
                if ($dt_Payway_tui[$j]["退款方式"] == $dt_Payway[$i]["支付方式"]) {
                    $dt_Payway[$i]["金额"] = round(floatval($dt_Payway[$i]["金额"]) - floatval($dt_Payway_tui[$j]["金额"]), 2);
                }
            }
        }
        for ($i = 0; $i < count($dt_Payway_tui); $i++) {
            $flag = false;
            for ($j = 0; $j < count($dt_Payway); $j++) {
                if ($dt_Payway[$j]["支付方式"] == $dt_Payway_tui[$i]["退款方式"]) {
                    $flag = true;
                    break;
                }
            }
            if ($flag == false) {
                $dt_Payway_tui[$i]["金额"] = round(-floatval($dt_Payway_tui[$i]["金额"]), 2);
                $dt_Payway[] = $dt_Payway_tui[$i];
            }
        }

        return $dt_Payway;
    }

    public function yinbaoamount($overallnum = null)
    {
        //        $map['overallNum']=$overallnum;
        //        $data=M('sp_overalldaliy')->where($map)->find();
        //        $beginTime=$data['beginTime'];
        //        $endTime=$data['endTime'];
        //        $map2['createat'] = array('between',array($beginTime,$endTime));
        //        $map2['remark'] = array('like','银豹系统转入%');
        //        $map2['openCardShopId'] = $data['shopId'];
        //        $data2=M('sp_membercard')->where($map2)->select();
        //        $this->assign('_list',$data2);
        //        $sumAmount=M('sp_membercard')->where($map2)->sum('amount');
        //        $this->assign('sumAmount',$sumAmount);
        //        $this->display();

        $Model = new Model();
        $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            $beginTime = $temp[0]["beginTime"];
            $endTime = $temp[0]["endTime"];
            $shopId = $temp[0]["shopId"];

            $sql_member = "select cardNum,name,cardhardNum,amount from gr_sp_membercard where openCardShopId=$shopId and openCardDate>$beginTime and openCardDate<$endTime and remark like '银豹系统转入%'";
            $dt_member = $Model->query($sql_member);

            $allAmount = 0;
            for ($i = 0; $i < count($dt_member); $i++) {
                $cardhardNum = $dt_member[$i]["cardhardNum"];
                $sql_memberDetailAdd = "select sum(count) as counts from gr_sp_membercarddetail WHERE cardhardNum='$cardhardNum' and date>$beginTime  and (type=2 or type=3) ";//and date < $endTime
                $sql_memberDetailMinus = "select sum(count) as counts from gr_sp_membercarddetail WHERE cardhardNum='$cardhardNum' and date>$beginTime  and type=1 ";//and date < $endTime

                $memberDetailAdd = $Model->query($sql_memberDetailAdd);
                $memberDetailMinus = $Model->query($sql_memberDetailMinus);

                $memberDetailAddCount = 0;
                $memberDetailMinusCount = 0;
                if ($memberDetailAdd != null && count($memberDetailAdd) > 0) {
                    $memberDetailAddCount = $memberDetailAdd[0]["counts"];
                }
                if ($memberDetailMinus != null && count($memberDetailMinus) > 0) {
                    $memberDetailMinusCount = $memberDetailMinus[0]["counts"];
                }

                $dt_member[$i]["amount"] = floatval($dt_member[$i]["amount"]) + floatval($memberDetailMinusCount) - floatval($memberDetailAddCount);
                $allAmount += floatval($dt_member[$i]["amount"]);
            }

            $this->assign('_list', $dt_member);
            $this->assign('sumAmount', $allAmount);
        }
        $this->display();
    }

    public function meirichongzhi($overallnum = null)
    {
        $map['overallNum'] = $overallnum;
        $data = M('sp_overalldaliy')->where($map)->find();
        $beginTime = $data['beginTime'];
        $endTime = $data['endTime'];
        $id = $data['shopId'];

        $map2['date'] = array('between', array($beginTime, $endTime));
        $map2['type'] = 2;
        $map2['orderNum'] = array('like', '990' . $id . '%');
        $map2['remark'] = '会员卡现金充值';
        $map2['status'] = 1;
        $sumChargeCount1 = M('sp_membercarddetail')->where($map2)->sum('chargeCount');
        $sumGiveCount1 = M('sp_membercarddetail')->where($map2)->sum('giveCount');

        $map3['date'] = array('between', array($beginTime, $endTime));
        $map3['type'] = 2;
        $map3['orderNum'] = array('like', '990' . $id . '%');
        $map3['remark'] = '会员卡银联充值';
        $map3['status'] = 1;
        $sumChargeCount2 = M('sp_membercarddetail')->where($map3)->sum('chargeCount');
        $sumGiveCount2 = M('sp_membercarddetail')->where($map3)->sum('giveCount');

        $map5['date'] = array('between', array($beginTime, $endTime));
        $map5['type'] = 2;
        $map5['orderNum'] = array('like', '990' . $id . '%');
        $map5['remark'] = '会员卡支付宝充值';
        $map5['status'] = 1;
        $sumChargeCount3 = M('sp_membercarddetail')->where($map5)->sum('chargeCount');
        $sumGiveCount3 = M('sp_membercarddetail')->where($map5)->sum('giveCount');

        $map4['date'] = array('between', array($beginTime, $endTime));
        $map4['type'] = 2;
        $map4['orderNum'] = array('like', '990' . $id . '%');
        $map4['status'] = 1;
        $data2 = M('sp_membercarddetail')->where($map4)->order('remark asc')->select();
        $this->assign('_list', $data2);

        $this->assign('sumChargeCount1', $sumChargeCount1);
        $this->assign('sumGiveCount1', $sumGiveCount1);
        $this->assign('sumChargeCount2', $sumChargeCount2);
        $this->assign('sumGiveCount2', $sumGiveCount2);
        $this->assign('sumChargeCount3', $sumChargeCount3);
        $this->assign('sumGiveCount3', $sumGiveCount3);
        $this->display();
    }

    public function overallmonth($shopId = null, $beginTime = null, $endTime = null)
    {
        if ($shopId <= 0 || $beginTime == null || $endTime == null) {
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->display();
        } else {
            $sellAmountAll_month = 0;
            $payAmountAll_month = 0;
            $beginTimeOutPut = $beginTime;
            $endTimeOutPut = $endTime;
            $dtRes = $this->calculateoverallmonth($shopId, $beginTime, $endTime);
            for ($i = 0; $i < count($dtRes); $i++) {
                $sellAmountAll_month += floatval($dtRes[$i][13]);
                $payAmountAll_month += floatval($dtRes[$i][21]);
            }
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->assign('shopid', $shopId);
            $this->assign('beginTime', $beginTimeOutPut);
            $this->assign('endTime', $endTimeOutPut);
            $this->assign("_list", $dtRes);
            $this->assign("sellAmountAll_month", $sellAmountAll_month);
            $this->assign("payAmountAll_month", $payAmountAll_month);
            $this->display();
        }
//        $dt_Payway = $this->getPaywayByCategory($overallnum);
//        $this->assign("_list", $dt_Payway);
//        $this->assign("overallNum", $overallnum);
//
//        $this->display("overalldaliygetpaydetail");
    }

    private function calculateoverallmonth($shopId = null, $beginTime = null, $endTime = null)
    {
        $dtRes = [];
        $dateBegin = strtotime($beginTime);
        $dateEnd = strtotime("$endTime + 1 day");

        $Model = new Model();
        $sql = "select overallNum from gr_sp_overalldaliy where endTime>$dateBegin and endTime<$dateEnd  and shopId=$shopId";
        $temp = $Model->query($sql);

        if ($temp != null && count($temp) > 0) {
            for ($i = 0; $i < count($temp); $i++) {
                $overallnum = $temp[$i]["overallNum"];
                $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
                $temp2 = $Model->query($sql);
                if ($temp2 != null && count($temp2) > 0) {
                    $beginTime = $temp2[0]["beginTime"];
                    $endTime = $temp2[0]["endTime"];
                    $shopId = $temp2[0]["shopId"];

                    $minOrderNum = "";
                    $maxOrderNum = "";
                    $sqlOrderMinMax = "select min(orderNum) as minOrderNum,max(orderNum) as maxOrderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (type=1 or type=3 or type=5)";
                    $dtOrderMinMax = $Model->query($sqlOrderMinMax);
                    if ($dtOrderMinMax != null && count($dtOrderMinMax) > 0) {
                        $minOrderNum = $dtOrderMinMax[0]["minOrderNum"];
                        $maxOrderNum = $dtOrderMinMax[0]["maxOrderNum"];
                    }
//                    $sqlOffLine = "select '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and pc.pid<=7 and o.type=1 and o.status=1 group by pc.pid";
//                    $sqlGroup = "select '' as 类型, pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and pc.pid<=7 and o.type=5 and o.status=1 group by pc.pid";
//                    //线上的只要果汁
//                    $sqlOnline = "select '' as 类型, pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and pc.pid=1 and o.type=9 and o.status=1 group by pc.pid";
//                    $sqlOffLine_Tui = "select '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and pc.pid<=7 and o.type=3 and o.status=1 group by pc.pid";
//                    //$sqlOnline_Tui = "select '' as 类型, pc.pid as gPid,sum(psc.changeCount) as gCount,-1 as gPrice from (gr_sp_pstockchangedetail psc inner join gr_sp_product p on psc.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and psc.createat>$beginTime and psc.createat<$endTime and psc.shopId=$shopId and psc.type=7 and psc.status=1 group by pc.pid";
//
//                    $dtOffLine = $Model->query($sqlOffLine);
//                    $dtGroup = $Model->query($sqlGroup);
//                    $dtOnline = $Model->query($sqlOnline);
//                    $dtOffLine_Tui = $Model->query($sqlOffLine_Tui);
//                    //$dtOnline_Tui = $Model->query($sqlOnline_Tui);

                    $dtOffLine = [];
                    $dtGroup = [];
                    $dtOnline = [];
                    $dtOffLine_Tui = [];
                    //$sqlAll = "select  o.type, '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and pc.pid<=7 and (o.type=1 or o.type=5 or o.type=9 or o.type=3) and o.status=1 group by pc.pid,o.type";
                    //$sqlAll = "select  t.type, '' as 类型,pc.pid as gPid,sum(t.count) as gCount,sum(t.finnalPrice) as gPrice from (select * from (select * from  gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t inner join gr_sp_product p on t.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where  pc.pid<=7 and (t.type=1 or t.type=5 or t.type=9 or t.type=3) and t.status=1 group by pc.pid,t.type";
                    //$sqlAll = "select  tttt.type, '' as 类型,tttt.pid as gPid,sum(tttt.count) as gCount,sum(tttt.finnalPrice) as gPrice from (select ttt.*,pc.pid,pc.type as pctype from( select tt.*,p.pCategoryId from( SELECT t.*,op.productId,op.count,op.finnalPrice from (select o.type,o.status,o.orderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t  inner join gr_sp_orderproduct op on t.orderNum=op.orderNum)tt inner join gr_sp_product p on tt.productId=p.id) ttt inner join gr_sp_pcategory pc on ttt.pCategoryId=pc.id  where pc.pid<=7 and (ttt.type=1 or ttt.type=5 or ttt.type=9 or ttt.type=3) and ttt.status=1 )tttt group by tttt.pid,tttt.pctype";
                    //$sqlAll = "select  tttt.type, '' as 类型,tttt.pid as gPid,sum(tttt.count) as gCount,sum(tttt.finnalPrice) as gPrice from (select ttt.*,pc.pid,pc.type as pctype from( select tt.*,p.pCategoryId from( SELECT t.*,op.productId,op.count,op.finnalPrice from (select o.type,o.status,o.orderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t  inner join (select * from  gr_sp_orderproduct where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') op on t.orderNum=op.orderNum)tt inner join gr_sp_product p on tt.productId=p.id) ttt inner join gr_sp_pcategory pc on ttt.pCategoryId=pc.id  where pc.pid<=7 and (ttt.type=1 or ttt.type=5 or ttt.type=9 or ttt.type=3) and ttt.status=1 )tttt group by tttt.pid,tttt.pctype";
                    $sqlAll="select  tttt.ordertype, '' as 类型,tttt.pid as gPid,sum(tttt.count) as gCount,sum(tttt.finnalPrice) as gPrice from(select ttt.*,pc.pid from( select tt.*,p.pCategoryId from( SELECT t.*,op.productId,op.count,op.finnalPrice from (select o.type as ordertype,o.status,o.orderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime  and o.createat<$endTime and (o.type=1 or o.type=5 or o.type=9 or o.type=3)) t  inner join (select * from  gr_sp_orderproduct where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') op on t.orderNum=op.orderNum)tt inner join gr_sp_product p on tt.productId=p.id) ttt inner join gr_sp_pcategory pc on ttt.pCategoryId=pc.id  where pc.pid<=7  and ttt.status=1)tttt group by tttt.pid,tttt.ordertype";
                    $dtAll = $Model->query($sqlAll);
                    for ($a = 0; $a < count($dtAll); $a++) {
                        if ($dtAll[$a]["ordertype"] == 1) {
                            $dtOffLine[] = $dtAll[$a];
                        }
                        if ($dtAll[$a]["ordertype"] == 5) {
                            $dtGroup[] = $dtAll[$a];
                        }
                        if ($dtAll[$a]["ordertype"] == 9) {
                            $dtOnline[] = $dtAll[$a];
                        }
                        if ($dtAll[$a]["ordertype"] == 3) {
                            $dtOffLine_Tui[] = $dtAll[$a];
                        }
                    }

                    //扣除退货
                    for ($j = 0; $j < count($dtOffLine_Tui); $j++) {
                        for ($k = 0; $k < count($dtOffLine); $k++) {
                            if ($dtOffLine_Tui[$j]["gPid"] == $dtOffLine[$k]["gPid"]) {
                                $dtOffLine[$k]["gPrice"] = floatval($dtOffLine[$k]["gPrice"]) - floatval($dtOffLine_Tui[$j]["gPrice"]);
                            }
                        }
                    }
                    $sellAmountAll = 0;
                    $dateFlag = date('m-d', $endTime);
                    $dtResItem = [$dateFlag, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
                    for ($m = 0; $m < count($dtOffLine); $m++) {
                        if ($dtOffLine[$m]["gPid"] == 1) {
                            $dtResItem[1] = $dtOffLine[$m]["gPrice"];
                        }
                        if ($dtOffLine[$m]["gPid"] == 2) {
                            $dtResItem[2] = $dtOffLine[$m]["gPrice"];
                        }
                        if ($dtOffLine[$m]["gPid"] == 3) {
                            $dtResItem[3] = $dtOffLine[$m]["gPrice"];
                        }
                        if ($dtOffLine[$m]["gPid"] == 4) {
                            $dtResItem[4] = $dtOffLine[$m]["gPrice"];
                        }
                        if ($dtOffLine[$m]["gPid"] == 5) {
                            $dtResItem[5] = $dtOffLine[$m]["gPrice"];
                        }
                        if ($dtOffLine[$m]["gPid"] == 6) {
                            $dtResItem[6] = $dtOffLine[$m]["gPrice"];
                        }
                        if ($dtOffLine[$m]["gPid"] == 7) {
                            $dtResItem[7] = $dtOffLine[$m]["gPrice"];
                        }
                        $sellAmountAll += floatval($dtOffLine[$m]["gPrice"]);
                    }
                    for ($m = 0; $m < count($dtGroup); $m++) {
                        if ($dtGroup[$m]["gPid"] == 1) {
                            $dtResItem[8] = $dtGroup[$m]["gPrice"];
                        }
                        if ($dtGroup[$m]["gPid"] == 2) {
                            $dtResItem[9] = $dtGroup[$m]["gPrice"];
                        }
                        if ($dtGroup[$m]["gPid"] == 3) {
                            $dtResItem[10] = $dtGroup[$m]["gPrice"];
                        }
                        if ($dtGroup[$m]["gPid"] == 4) {
                            $dtResItem[11] = $dtGroup[$m]["gPrice"];
                        }
                        $sellAmountAll += floatval($dtGroup[$m]["gPrice"]);
                    }
                    for ($m = 0; $m < count($dtOnline); $m++) {
                        if ($dtOnline[$m]["gPid"] == 1) {
                            $dtResItem[12] = $dtOnline[$m]["gPrice"];
                        }
                    }
                    $dtResItem[13] = $sellAmountAll;

                    $payAmountAll = 0;
                    $dtPayway = $this->getPaywayByCategory($overallnum);
                    for ($n = 0; $n < count($dtPayway); $n++) {
                        if ($dtPayway[$n]["支付方式"] == "现金") {
                            $dtResItem[14] = $dtPayway[$n]["金额"];
                        }
                        if ($dtPayway[$n]["支付方式"] == "银联") {
                            $dtResItem[15] = $dtPayway[$n]["金额"];
                        }
                        if ($dtPayway[$n]["支付方式"] == "会员卡") {
                            $dtResItem[16] = $dtPayway[$n]["金额"];
                        }
                        if ($dtPayway[$n]["支付方式"] == "支付宝") {
                            $dtResItem[17] = $dtPayway[$n]["金额"];
                        }
                        if ($dtPayway[$n]["支付方式"] == "美团") {
                            $dtResItem[18] = $dtPayway[$n]["金额"];
                        }
                        if ($dtPayway[$n]["支付方式"] == "百度糯米") {
                            $dtResItem[19] = $dtPayway[$n]["金额"];
                        }
                        if ($dtPayway[$n]["支付方式"] == "饿了么") {
                            $dtResItem[20] = $dtPayway[$n]["金额"];
                        }
                        $payAmountAll += floatval($dtPayway[$n]["金额"]);
                    }
                    $dtResItem[21] = $payAmountAll;
                    $dtRes[] = $dtResItem;
                }
            }
        }
        return $dtRes;
    }

    //月结充值统计
    public function overallmonthcharge($shopId = null, $beginTime = null, $endTime = null)
    {
        $totalChargeAmount = 0;
        $totalChargeAmount_cash = 0;
        $totalChargeAmount_unionpay = 0;
        $totalChargeAmount_alipay = 0;
        if ($shopId <= 0 || $beginTime == null || $endTime == null) {
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->display();
        } else {
            $beginTimeOutPut = $beginTime;
            $endTimeOutPut = $endTime;
            $dateBegin = strtotime($beginTime);
            $dateEnd = strtotime("$endTime + 1 day");
            $dtRes = $this->calculateoverallmonthcharge($shopId, $beginTime, $endTime);
            for ($i = 0; $i < count($dtRes); $i++) {
                $totalChargeAmount_cash += floatval($dtRes[$i][4]);
                $totalChargeAmount_unionpay += floatval($dtRes[$i][5]);
                $totalChargeAmount_alipay += floatval($dtRes[$i][6]);
            }

            $Model = new Model();
            $sql = "select count(*) as totalCount from gr_sp_order o INNER join gr_sp_membercarddetail md on o.orderNum=md.orderNum WHERE o.type=2 and o.shopId=$shopId and md.date>$dateBegin and md.date<$dateEnd and o.status=1 and md.status=1";
            $chargeCount = $Model->query($sql);//充值笔数
            $chargeCount = $chargeCount[0]["totalCount"];

            $sql = "select count(*) as totalCount from  gr_sp_membercard   WHERE  openCardShopId=$shopId  and createat>$dateBegin and createat<$dateEnd and remark not like '%银豹系统转入%' and status=1 and delflag=0";
            $membercardCount = $Model->query($sql);//新办卡
            $membercardCount = $membercardCount[0]["totalCount"];

            $totalChargeAmount = $totalChargeAmount_cash + $totalChargeAmount_unionpay + $totalChargeAmount_alipay;
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->assign('shopid', $shopId);
            $this->assign('beginTime', $beginTimeOutPut);
            $this->assign('endTime', $endTimeOutPut);
            $this->assign("_list", $dtRes);
            $this->assign("totalChargeAmount", $totalChargeAmount);
            $this->assign("totalChargeAmount_cash", $totalChargeAmount_cash);
            $this->assign("totalChargeAmount_unionpay", $totalChargeAmount_unionpay);
            $this->assign("totalChargeAmount_alipay", $totalChargeAmount_alipay);
            $this->assign("chargeCount", $chargeCount);
            $this->assign("membercardCount", $membercardCount);
            $this->display();
        }
    }

    private function calculateoverallmonthcharge($shopId = null, $beginTime = null, $endTime = null)
    {
        $dtRes = [];
        $dateBegin = strtotime($beginTime);
        $dateEnd = strtotime("$endTime + 1 day");
        $Model = new Model();
        $sql = "select overallNum from gr_sp_overalldaliy where endTime>$dateBegin and endTime<$dateEnd  and shopId=$shopId";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            for ($i = 0; $i < count($temp); $i++) {
                $overallnum = $temp[$i]["overallNum"];
                $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
                $temp2 = $Model->query($sql);
                if ($temp2 != null && count($temp2) > 0) {
                    $beginTime = $temp2[0]["beginTime"];
                    $endTime = $temp2[0]["endTime"];
                    $shopId = $temp2[0]["shopId"];

                    $dtResItem = $this->getChargeData($shopId, $beginTime, $endTime);
                    $dtRes[] = $dtResItem;
                }
            }
        }
        return $dtRes;
    }

    private function getChargeData($shopId, $beginTime, $endTime)
    {
        $Model = new Model();
        $sql = "select sum(md.chargeCount) as totalCharge, sum(md.giveCount) as totalGive,md.remark from gr_sp_order o INNER join gr_sp_membercarddetail md on o.orderNum=md.orderNum left join gr_sp_membercard m on md.membercardId=m.id where o.type=2 and o.shopId=$shopId and md.date>$beginTime and md.date<$endTime and md.status=1 group by md.remark";
        $temp3 = $Model->query($sql);
        $dateFlag = date('m-d', $endTime);
        $dtResItem = [$dateFlag, 0, 0, 0, 0, 0, 0, 0];
        for ($j = 0; $j < count($temp3); $j++) {
            if ($temp3[$j]["remark"] == "会员卡现金充值") {
                $dtResItem[4] = $temp3[$j]["totalCharge"];
                //$totalChargeAmount_cash += floatval($temp3[$j]["totalCharge"]);
            }
            if ($temp3[$j]["remark"] == "会员卡银联充值") {
                $dtResItem[5] = $temp3[$j]["totalCharge"];
                //$totalChargeAmount_unionpay += floatval($temp3[$j]["totalCharge"]);
            }
            if ($temp3[$j]["remark"] == "会员卡支付宝充值") {
                $dtResItem[6] = $temp3[$j]["totalCharge"];
                //$totalChargeAmount_unionpay += floatval($temp3[$j]["totalCharge"]);
            }
            $dtResItem[2] += floatval($temp3[$j]["totalGive"]);
        }
        $dtResItem[7] = floatval($dtResItem[4]) + floatval($dtResItem[5]) + floatval($dtResItem[6]);
        $dtResItem[1] = $dtResItem[7];
        $dtResItem[3] = floatval($dtResItem[1]) + floatval($dtResItem[2]);
        return $dtResItem;
    }

    //月银豹转入金额统计
    public function overallmonthyinbaotransfer($shopId = null, $beginTime = null, $endTime = null)
    {
        $totalyinbaotransferAmount = 0;
        if ($shopId <= 0 || $beginTime == null || $endTime == null) {
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->display();
        } else {
            $beginTimeOutPut = $beginTime;
            $endTimeOutPut = $endTime;
            $dtRes = $this->calculateoverallmonthyinbaotransfer($shopId, $beginTime, $endTime);
            for ($i = 0; $i < count($dtRes); $i++) {
                $totalyinbaotransferAmount += floatval($dtRes[$i][1]);
            }
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->assign('shopid', $shopId);
            $this->assign('beginTime', $beginTimeOutPut);
            $this->assign('endTime', $endTimeOutPut);
            $this->assign("_list", $dtRes);
            $this->assign("totalyinbaotransferAmount", $totalyinbaotransferAmount);
            $this->display();
        }
    }

    private function calculateoverallmonthyinbaotransfer($shopId = null, $beginTime = null, $endTime = null)
    {
        $dtRes = [];
        $dateBegin = strtotime($beginTime);
        $dateEnd = strtotime("$endTime + 1 day");
        $Model = new Model();
        $sql = "select overallNum from gr_sp_overalldaliy where endTime>$dateBegin and endTime<$dateEnd  and shopId=$shopId";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            for ($i = 0; $i < count($temp); $i++) {
                $overallnum = $temp[$i]["overallNum"];
                $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
                $temp2 = $Model->query($sql);
                if ($temp2 != null && count($temp2) > 0) {
                    $beginTime = $temp2[0]["beginTime"];
                    $endTime = $temp2[0]["endTime"];
                    $shopId = $temp2[0]["shopId"];

                    $sql_member = "select cardNum,name,cardhardNum,amount from gr_sp_membercard where openCardShopId=$shopId and openCardDate>$beginTime and openCardDate<$endTime and remark like '银豹系统转入%'";
                    $dt_member = $Model->query($sql_member);

                    $allAmount = 0;
                    for ($j = 0; $j < count($dt_member); $j++) {
                        $cardhardNum = $dt_member[$j]["cardhardNum"];
                        $sql_memberDetailAdd = "select sum(count) as counts from gr_sp_membercarddetail WHERE cardhardNum='$cardhardNum' and date>$beginTime  and (type=2 or type=3) ";//and date < $endTime
                        $sql_memberDetailMinus = "select sum(count) as counts from gr_sp_membercarddetail WHERE cardhardNum='$cardhardNum' and date>$beginTime  and type=1 ";//and date < $endTime

                        $memberDetailAdd = $Model->query($sql_memberDetailAdd);
                        $memberDetailMinus = $Model->query($sql_memberDetailMinus);

                        $memberDetailAddCount = 0;
                        $memberDetailMinusCount = 0;
                        if ($memberDetailAdd != null && count($memberDetailAdd) > 0) {
                            $memberDetailAddCount = $memberDetailAdd[0]["counts"];
                        }
                        if ($memberDetailMinus != null && count($memberDetailMinus) > 0) {
                            $memberDetailMinusCount = $memberDetailMinus[0]["counts"];
                        }

                        $dt_member[$j]["amount"] = floatval($dt_member[$j]["amount"]) + floatval($memberDetailMinusCount) - floatval($memberDetailAddCount);
                        $allAmount += floatval($dt_member[$j]["amount"]);
                    }
                    $dateFlag = date('m-d', $endTime);
                    $dtResItem = [$dateFlag, $allAmount];
                    $dtRes[] = $dtResItem;
                }
            }
        }
        return $dtRes;
    }

    //每日盘点
    public function checkstockdaliy($overallnum = null)
    {
        $Model = new Model();
        $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
        $temp = $Model->query($sql);
        $checkList = [];
        if ($temp != null && count($temp) > 0) {
            $beginTime = $temp[0]["beginTime"];
            $endTime = $temp[0]["endTime"];
            $shopId = $temp[0]["shopId"];

            $sql = "select c.shopId,p.name,c.theoryStock,c.realStock,c.minus,c.plus,c.used,c.diffeValue,c.remark,c.checkat from gr_sp_checkstock c INNER join gr_sp_product p on c.productId=p.id where c.shopId=$shopId and c.checkat>$beginTime and c.checkat<$endTime and c.status=1";

            $checkList = $Model->query($sql);
        }
        $this->assign("_list", $checkList);
        $this->display();
    }

    public function ExportOverallMonth($shopId = null, $beginTime = null, $endTime = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/overallDaliy");
        } else {
            if ($shopId <= 0 || $beginTime == null || $endTime == null) {
                $this->error("参数有误！");
            } else {
                $dataTable = $this->calculateoverallmonth($shopId, $beginTime, $endTime);
                if ($dataTable) {

                    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                    import("Org.Util.PHPExcel");
                    import("Org.Util.PHPExcel.Writer.Excel5");
                    import("Org.Util.PHPExcel.IOFactory.php");

                    $filename = "$shopId.月结对账报表";

                    $headArr = array("日期", "直接销售果汁", "直接销售水果", "直接销售小吃", "直接销售套餐", "直接销售处理品", "直接销售耗材", "直接销售服务费", "团购果汁", "团购水果", "团购小吃", "团购套餐", "线上水果", "销售合计", "现金", "银联", "会员卡", "支付宝", "美团", "百度", "饿了么", "支付合计");

                    $title = "月结对账报表";
                    $this->getExcel_yue($filename, $headArr, $dataTable, $title);
                } else {
                    $this->error("没有可导出的数据！");
                }
            }
        }
    }

    public function ExportOverallMonthCharge($shopId = null, $beginTime = null, $endTime = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/overallDaliy");
        } else {
            if ($shopId <= 0 || $beginTime == null || $endTime == null) {
                $this->error("参数有误！");
            } else {
                $dataTable = $this->calculateoverallmonthcharge($shopId, $beginTime, $endTime);
                if ($dataTable) {

                    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                    import("Org.Util.PHPExcel");
                    import("Org.Util.PHPExcel.Writer.Excel5");
                    import("Org.Util.PHPExcel.IOFactory.php");

                    $filename = "$shopId.月结充值报表";

                    $headArr = array("日期", "面值金额", "赠送金额", "合计", "现金", "刷卡", "支付宝", "支付合计");

                    $title = "月结充值报表";
                    $this->getExcel_yue($filename, $headArr, $dataTable, $title);
                } else {
                    $this->error("没有可导出的数据！");
                }
            }
        }
    }

    public function ExportOverallyinbaoTransfer($shopId = null, $beginTime = null, $endTime = null)
    {
        if (!IS_POST) {
            $this->redirect("SpReport/overallDaliy");
        } else {
            if ($shopId <= 0 || $beginTime == null || $endTime == null) {
                $this->error("参数有误！");
            } else {
                $dataTable = $this->calculateoverallmonthyinbaotransfer($shopId, $beginTime, $endTime);
                if ($dataTable) {

                    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                    import("Org.Util.PHPExcel");
                    import("Org.Util.PHPExcel.Writer.Excel5");
                    import("Org.Util.PHPExcel.IOFactory.php");

                    $filename = "$shopId.月结银豹转入金额报表";

                    $headArr = array("日期", "转入金额");

                    $title = "月结银豹转入金额报表";
                    $this->getExcel_yue($filename, $headArr, $dataTable, $title);
                } else {
                    $this->error("没有可导出的数据！");
                }
            }
        }
    }

    //实时数据
    public function overallactualtime($shopId = null, $beginTime = null, $endTime = null)
    {
        if ($shopId <= 0 || $beginTime == null || $endTime == null) {
            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->display();
        } else {
            $sellAmountAll_month = 0;
            $payAmountAll_month = 0;
            $beginTimeOutPut = $beginTime;
            $endTimeOutPut = $endTime;
            $dtRes = $this->calculateoverallactualtime($shopId, $beginTime, $endTime);
            for ($i = 0; $i < count($dtRes); $i++) {
                $sellAmountAll_month += floatval($dtRes[$i][13]);
                $payAmountAll_month += floatval($dtRes[$i][21]);
            }
            //总单量
            $beginTime = strtotime($beginTime);
            $endTime = strtotime($endTime);
            $Model = new Model();
            $sqlOrderCount_usefull = "select count(id) as counts from gr_sp_order where shopId=$shopId and createat>$beginTime and createat<$endTime and(type=1 or type=5) and status=1";
            $sqlOrderCount_cancel = "select count(id) as counts from gr_sp_order where shopId=$shopId and createat>$beginTime and createat<$endTime and(type=1 or type=3 or type=5) and status=0";
            $sqlOrderCount_tui = "select count(id) as counts from gr_sp_order where shopId=$shopId and createat>$beginTime and createat<$endTime and  type=3 and status=1";
            $dtOrderCount_usefull = $Model->query($sqlOrderCount_usefull);
            $dtOrderCount_cancel = $Model->query($sqlOrderCount_cancel);
            $dtOrderCount_tui = $Model->query($sqlOrderCount_tui);

            $shoplist = M('sp_shops')->where(array('status' => 1))->select();
            $this->assign('shoplist', $shoplist);
            $this->assign('shopid', $shopId);
            $this->assign('beginTime', $beginTimeOutPut);
            $this->assign('endTime', $endTimeOutPut);
            $this->assign("_list", $dtRes);
            $this->assign("sellAmountAll_month", $sellAmountAll_month);
            $this->assign("payAmountAll_month", $payAmountAll_month);
            $this->assign("orderCount_usefull", $dtOrderCount_usefull[0]["counts"]);
            $this->assign("orderCount_cancel", $dtOrderCount_cancel[0]["counts"]);
            $this->assign("orderCount_tui", $dtOrderCount_tui[0]["counts"]);


            $dtResChargeItem = $this->getChargeData($shopId, $beginTime, $endTime);
            $dtResCharge[] = $dtResChargeItem;
            $totalChargeAmount_cash = 0;
            $totalChargeAmount_unionpay = 0;
            $totalChargeAmount_alipay = 0;
            for ($i = 0; $i < count($dtResCharge); $i++) {
                $totalChargeAmount_cash += floatval($dtResCharge[$i][4]);
                $totalChargeAmount_unionpay += floatval($dtResCharge[$i][5]);
                $totalChargeAmount_alipay += floatval($dtResCharge[$i][6]);
            }

            $Model = new Model();
            $sql = "select count(*) as totalCount from gr_sp_order o INNER join gr_sp_membercarddetail md on o.orderNum=md.orderNum WHERE o.type=2 and o.shopId=$shopId and md.date>$beginTime and md.date<$endTime and o.status=1 and md.status=1";
            $chargeCount = $Model->query($sql);//充值笔数
            $chargeCount = $chargeCount[0]["totalCount"];

            $sql = "select count(*) as totalCount from  gr_sp_membercard   WHERE  openCardShopId=$shopId  and createat>$beginTime and createat<$endTime and remark not like '%银豹系统转入%' and status=1 and delflag=0";
            $membercardCount = $Model->query($sql);//新办卡
            $membercardCount = $membercardCount[0]["totalCount"];

            $totalChargeAmount = $totalChargeAmount_cash + $totalChargeAmount_unionpay + $totalChargeAmount_alipay;
            $this->assign("_listCharge", $dtResCharge);
            $this->assign("totalChargeAmount", $totalChargeAmount);
            $this->assign("totalChargeAmount_cash", $totalChargeAmount_cash);
            $this->assign("totalChargeAmount_unionpay", $totalChargeAmount_unionpay);
            $this->assign("totalChargeAmount_alipay", $totalChargeAmount_alipay);
            $this->assign("chargeCount", $chargeCount);
            $this->assign("membercardCount", $membercardCount);

            $this->display();
        }
    }

    private function calculateoverallactualtime($shopId = null, $beginTime = null, $endTime = null)
    {
        $dtRes = [];
        $dateBegin = strtotime($beginTime);
        $dateEnd = strtotime($endTime);

        $Model = new Model();
        $beginTime = $dateBegin;
        $endTime = $dateEnd;

        $minOrderNum = "";
        $maxOrderNum = "";
        $sqlOrderMinMax = "select min(orderNum) as minOrderNum,max(orderNum) as maxOrderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and (type=1 or type=3 or type=5)";
        $dtOrderMinMax = $Model->query($sqlOrderMinMax);
        if ($dtOrderMinMax != null && count($dtOrderMinMax) > 0) {
            $minOrderNum = $dtOrderMinMax[0]["minOrderNum"];
            $maxOrderNum = $dtOrderMinMax[0]["maxOrderNum"];
        }
//        $sqlOffLine = "select '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=1 and o.status=1 group by pc.pid";
//        $sqlGroup = "select '' as 类型, pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=5 and o.status=1 group by pc.pid";
//        //线上的只要果汁
//        $sqlOnline = "select '' as 类型, pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid=1 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=9 and o.status=1 group by pc.pid";
//        $sqlOffLine_Tui = "select '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and o.createat>$beginTime and o.createat<$endTime and o.shopId=$shopId and o.type=3 and o.status=1 group by pc.pid";
//        //$sqlOnline_Tui = "select '' as 类型, pc.pid as gPid,sum(psc.changeCount) as gCount,-1 as gPrice from (gr_sp_pstockchangedetail psc inner join gr_sp_product p on psc.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where pc.pid<=7 and psc.createat>$beginTime and psc.createat<$endTime and psc.shopId=$shopId and psc.type=7 and psc.status=1 group by pc.pid";
//
//        $dtOffLine = $Model->query($sqlOffLine);
//        $dtGroup = $Model->query($sqlGroup);
//        $dtOnline = $Model->query($sqlOnline);
//        $dtOffLine_Tui = $Model->query($sqlOffLine_Tui);
//        //$dtOnline_Tui = $Model->query($sqlOnline_Tui);
        $dtOffLine = [];
        $dtGroup = [];
        $dtOnline = [];
        $dtOffLine_Tui = [];
        //$sqlAll = "select  o.type, '' as 类型,pc.pid as gPid,sum(op.count) as gCount,sum(op.finnalPrice) as gPrice from ((gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum) inner join gr_sp_product p on op.productId=p.id) inner join gr_sp_pcategory pc on p.pCategoryId=pc.id  where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime and pc.pid<=7 and (o.type=1 or o.type=5 or o.type=9 or o.type=3) and o.status=1 group by pc.pid,o.type";
        //$sqlAll = "select  ttt.type, '' as 类型,pc.pid as gPid,sum(ttt.count) as gCount,sum(ttt.finnalPrice) as gPrice from(select * from ( select tt.*,p.pCategoryId from (SELECT t.*,op.productId,op.count,op.finnalPrice from (select o.type,o.status,o.orderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t  inner join gr_sp_orderproduct op on t.orderNum=op.orderNum) tt inner join gr_sp_product p on tt.productId=p.id)ttt  inner join gr_sp_pcategory pc on ttt.pCategoryId=pc.id)  where pc.pid<=7 and (ttt.type=1 or ttt.type=5 or ttt.type=9 or ttt.type=3) and ttt.status=1 group by pc.pid,ttt.type";
        //$sqlAll = "select  tttt.type, '' as 类型,tttt.pid as gPid,sum(tttt.count) as gCount,sum(tttt.finnalPrice) as gPrice from (select ttt.*,pc.pid,pc.type as pctype from( select tt.*,p.pCategoryId from( SELECT t.*,op.productId,op.count,op.finnalPrice from (select o.type as ordertype,o.status,o.orderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t  inner join (select * from  gr_sp_orderproduct where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') op on t.orderNum=op.orderNum)tt inner join gr_sp_product p on tt.productId=p.id) ttt inner join gr_sp_pcategory pc on ttt.pCategoryId=pc.id  where pc.pid<=7 and (ttt.type=1 or ttt.type=5 or ttt.type=9 or ttt.type=3) and ttt.status=1 )tttt group by tttt.pid,tttt.pctype";
        $sqlAll="select  tttt.ordertype, '' as 类型,tttt.pid as gPid,sum(tttt.count) as gCount,sum(tttt.finnalPrice) as gPrice from(select ttt.*,pc.pid from( select tt.*,p.pCategoryId from( SELECT t.*,op.productId,op.count,op.finnalPrice from (select o.type as ordertype,o.status,o.orderNum from gr_sp_order o where o.shopId=$shopId and o.createat>$beginTime  and o.createat<$endTime and (o.type=1 or o.type=5 or o.type=9 or o.type=3)) t  inner join (select * from  gr_sp_orderproduct where orderNum>='$minOrderNum' and orderNum<='$maxOrderNum') op on t.orderNum=op.orderNum)tt inner join gr_sp_product p on tt.productId=p.id) ttt inner join gr_sp_pcategory pc on ttt.pCategoryId=pc.id  where pc.pid<=7  and ttt.status=1)tttt group by tttt.pid,tttt.ordertype";
//        $sqlAllSub1="select * from ( select o.type,o.status,op.* from  gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t inner join gr_sp_product p on t.productId=p.id";
//        dump($sqlAllSub1);
//        $dtAllSub1 = $Model->query($sqlAllSub1);
//        dump($dtAllSub1);
//        $sqlAllSub="select * from (select * from (select o.type,o.status,op.* from  gr_sp_order o inner join gr_sp_orderproduct op on o.orderNum=op.orderNum where o.shopId=$shopId and o.createat>$beginTime and o.createat<$endTime) t inner join gr_sp_product p on t.productId=p.id) tt inner join gr_sp_pcategory pc on tt.pCategoryId=pc.id";
//        $dtAllSub = $Model->query($sqlAllSub);
//        dump($dtAllSub);
//        $sqlAll = "select  ttt.type, '' as 类型,ttt.pid as gPid,sum(ttt.count) as gCount,sum(ttt.finnalPrice) as gPrice from (SELECT * from (".$sqlAllSub.")ttt)  where  ttt.pid<=7 and (ttt.type=1 or ttt.type=5 or ttt.type=9 or ttt.type=3) and ttt.status=1 group by ttt.pid,ttt.type";
        $dtAll = $Model->query($sqlAll);
        for ($a = 0; $a < count($dtAll); $a++) {
            if ($dtAll[$a]["ordertype"] == 1) {
                $dtOffLine[] = $dtAll[$a];
            }
            if ($dtAll[$a]["ordertype"] == 5) {
                $dtGroup[] = $dtAll[$a];
            }
            if ($dtAll[$a]["ordertype"] == 9) {
                $dtOnline[] = $dtAll[$a];
            }
            if ($dtAll[$a]["ordertype"] == 3) {
                $dtOffLine_Tui[] = $dtAll[$a];
            }
        }
        //扣除退货
        for ($j = 0; $j < count($dtOffLine_Tui); $j++) {
            for ($k = 0; $k < count($dtOffLine); $k++) {
                if ($dtOffLine_Tui[$j]["gPid"] == $dtOffLine[$k]["gPid"]) {
                    $dtOffLine[$k]["gPrice"] = floatval($dtOffLine[$k]["gPrice"]) - floatval($dtOffLine_Tui[$j]["gPrice"]);
                }
            }
        }
        $sellAmountAll = 0;
        $dateFlag = date('m-d', $endTime);
        $dtResItem = [$dateFlag, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
        for ($m = 0; $m < count($dtOffLine); $m++) {
            if ($dtOffLine[$m]["gPid"] == 1) {
                $dtResItem[1] = $dtOffLine[$m]["gPrice"];
            }
            if ($dtOffLine[$m]["gPid"] == 2) {
                $dtResItem[2] = $dtOffLine[$m]["gPrice"];
            }
            if ($dtOffLine[$m]["gPid"] == 3) {
                $dtResItem[3] = $dtOffLine[$m]["gPrice"];
            }
            if ($dtOffLine[$m]["gPid"] == 4) {
                $dtResItem[4] = $dtOffLine[$m]["gPrice"];
            }
            if ($dtOffLine[$m]["gPid"] == 5) {
                $dtResItem[5] = $dtOffLine[$m]["gPrice"];
            }
            if ($dtOffLine[$m]["gPid"] == 6) {
                $dtResItem[6] = $dtOffLine[$m]["gPrice"];
            }
            if ($dtOffLine[$m]["gPid"] == 7) {
                $dtResItem[7] = $dtOffLine[$m]["gPrice"];
            }
            $sellAmountAll += floatval($dtOffLine[$m]["gPrice"]);
        }
        for ($m = 0; $m < count($dtGroup); $m++) {
            if ($dtGroup[$m]["gPid"] == 1) {
                $dtResItem[8] = $dtGroup[$m]["gPrice"];
            }
            if ($dtGroup[$m]["gPid"] == 2) {
                $dtResItem[9] = $dtGroup[$m]["gPrice"];
            }
            if ($dtGroup[$m]["gPid"] == 3) {
                $dtResItem[10] = $dtGroup[$m]["gPrice"];
            }
            if ($dtGroup[$m]["gPid"] == 4) {
                $dtResItem[11] = $dtGroup[$m]["gPrice"];
            }
            $sellAmountAll += floatval($dtGroup[$m]["gPrice"]);
        }
        for ($m = 0; $m < count($dtOnline); $m++) {
            if ($dtOnline[$m]["gPid"] == 1) {
                $dtResItem[12] = $dtOnline[$m]["gPrice"];
            }
        }
        $dtResItem[13] = $sellAmountAll;
        $payAmountAll = 0;
        $dtPayway = $this->calculatePaywayByCategory($beginTime, $endTime, $shopId);
        for ($n = 0; $n < count($dtPayway); $n++) {
            if ($dtPayway[$n]["支付方式"] == "现金") {
                $dtResItem[14] = $dtPayway[$n]["金额"];
            }
            if ($dtPayway[$n]["支付方式"] == "银联") {
                $dtResItem[15] = $dtPayway[$n]["金额"];
            }
            if ($dtPayway[$n]["支付方式"] == "会员卡") {
                $dtResItem[16] = $dtPayway[$n]["金额"];
            }
            if ($dtPayway[$n]["支付方式"] == "支付宝") {
                $dtResItem[17] = $dtPayway[$n]["金额"];
            }
            if ($dtPayway[$n]["支付方式"] == "美团") {
                $dtResItem[18] = $dtPayway[$n]["金额"];
            }
            if ($dtPayway[$n]["支付方式"] == "百度糯米") {
                $dtResItem[19] = $dtPayway[$n]["金额"];
            }
            if ($dtPayway[$n]["支付方式"] == "饿了么") {
                $dtResItem[20] = $dtPayway[$n]["金额"];
            }
            $payAmountAll += floatval($dtPayway[$n]["金额"]);
        }
        $dtResItem[21] = $payAmountAll;
        $dtRes[] = $dtResItem;

        return $dtRes;
    }

//    public function  shopsellsummary()
//    {
//        //获取当前年月
//        $thisMonth = date('Ym');
//        $thisDayBegin = strtotime(date('Y-m-d') . '00:00');
//        $thisDayEnd = strtotime(date('Y-m-d') . '23:59');
//        $yeaterdayBegin = strtotime(date("Y-m-d", strtotime("-1 day")) . '00:00');
//        $yeaterdayEnd = strtotime(date("Y-m-d", strtotime("-1 day")) . '23:59');
//        $thisMonthBegin = strtotime(date('Y-m') . '-01 00:00');
//        $thisMonthEnd = strtotime(date('Y-m-d H:i:s'));
//
//        $sqlThisDaySell = "select o.shopId,s.name,SUM(o.finnalPrice) as totalSell from gr_sp_order o inner join gr_sp_shops s on o.shopId=s.id WHERE  o.createat>$thisDayBegin and o.createat<$thisDayEnd and (o.type=1 or o.type=5) and o.status=1 group by o.shopId";
//        $sqlThisDaySell_Tui = "select o.shopId,s.name,SUM(o.finnalPrice) as totalSell from gr_sp_order o inner join gr_sp_shops s on o.shopId=s.id WHERE  o.createat>$thisDayBegin and o.createat<$thisDayEnd and o.type=3 and o.status=1 group by o.shopId";
//        $sqlYeaterdaySell = "select o.shopId,s.name,SUM(o.finnalPrice) as totalSell from gr_sp_order o inner join gr_sp_shops s on o.shopId=s.id WHERE  o.createat>$yeaterdayBegin and o.createat<$yeaterdayEnd and (o.type=1 or o.type=5) and o.status=1 group by o.shopId";
//        $sqlYeaterdaySell_Tui = "select o.shopId,s.name,SUM(o.finnalPrice) as totalSell from gr_sp_order o inner join gr_sp_shops s on o.shopId=s.id WHERE  o.createat>$yeaterdayBegin and o.createat<$yeaterdayEnd and o.type=3 and o.status=1 group by o.shopId";
//        $sqlThisMonthSell = "select o.shopId,s.name,SUM(o.finnalPrice) as totalSell from gr_sp_order o inner join gr_sp_shops s on o.shopId=s.id WHERE  o.createat>$thisMonthBegin and o.createat<$thisMonthEnd and (o.type=1 or o.type=5) and o.status=1 group by o.shopId";
//        $sqlThisMonthSell_Tui = "select o.shopId,s.name,SUM(o.finnalPrice) as totalSell from gr_sp_order o inner join gr_sp_shops s on o.shopId=s.id WHERE  o.createat>$thisMonthBegin and o.createat<$thisMonthEnd and o.type=3 and o.status=1 group by o.shopId";
//
//        $Model = new Model();
//        $dtThisDaySell = $Model->query($sqlThisDaySell);
//        $dtlThisDaySell_Tui = $Model->query($sqlThisDaySell_Tui);
//        $dtYeaterdaySell = $Model->query($sqlYeaterdaySell);
//        $dtYeaterdaySell_Tui = $Model->query($sqlYeaterdaySell_Tui);
//        $dtThisMonthSell = $Model->query($sqlThisMonthSell);
//        $dtThisMonthSell_Tui = $Model->query($sqlThisMonthSell_Tui);
//
//        for ($i = 0; $i < count($dtThisDaySell); $i++) {
//            for ($j = 0; $j < count($dtlThisDaySell_Tui); $j++) {
//                if ($dtThisDaySell[$i]["shopId"] == $dtlThisDaySell_Tui[$j]["shopId"]) {
//                    $dtThisDaySell[$i]["totalSell"] = floatval($dtThisDaySell[$i]["totalSell"]) - floatval($dtlThisDaySell_Tui[$i]["totalSell"]);
//                }
//            }
//        }
//
//        for ($i = 0; $i < count($dtYeaterdaySell); $i++) {
//            for ($j = 0; $j < count($dtYeaterdaySell_Tui); $j++) {
//                if ($dtYeaterdaySell[$i]["shopId"] == $dtYeaterdaySell_Tui[$j]["shopId"]) {
//                    $dtYeaterdaySell[$i]["totalSell"] = floatval($dtYeaterdaySell[$i]["totalSell"]) - floatval($dtYeaterdaySell_Tui[$i]["totalSell"]);
//                }
//            }
//        }
//
//        for ($i = 0; $i < count($dtThisMonthSell); $i++) {
//            for ($j = 0; $j < count($dtThisMonthSell_Tui); $j++) {
//                if ($dtThisMonthSell[$i]["shopId"] == $dtThisMonthSell_Tui[$j]["shopId"]) {
//                    $dtThisMonthSell[$i]["totalSell"] = floatval($dtThisMonthSell[$i]["totalSell"]) - floatval($dtThisMonthSell_Tui[$i]["totalSell"]);
//                }
//            }
//        }
//        $currentTime = time();
//        for ($i = 0; $i < count($dtThisDaySell); $i++) {
//            $shopId = $dtThisDaySell[$i]["shopId"];
//            $shopName = $dtThisDaySell[$i]["name"];
//            $total = $dtThisDaySell[$i]["totalSell"];
//            $sqlExist = "select id from gr_sp_sellsummary where shopId=$shopId and timePeriod =$thisMonth";
//            $dtExist = $Model->query($sqlExist);
//            $sql = '';
//            if ($dtExist[0] . count() == '') {
//                $sql = "insert into gr_sp_sellsummary (shopId,shopName,timePeriod,currentMonthSell,currentDaySell,yesterdaySell,completionRate,createAt,updateAt) values ($shopId,'$shopName','$thisMonth',0,$total,0,0,$currentTime,$currentTime)";
//                $res = $Model->execute($sql);
//            } else {
//                $sql = "update gr_sp_sellsummary set currentDaySell=$total,updateAt =$currentTime where shopId=$shopId and timePeriod ='$thisMonth' ";
//                $res = $Model->execute($sql);
//            }
//        }
//
//        for ($i = 0; $i < count($dtYeaterdaySell); $i++) {
//            $shopId = $dtYeaterdaySell[$i]["shopId"];
//            $shopName = $dtYeaterdaySell[$i]["name"];
//            $total = $dtYeaterdaySell[$i]["totalSell"];
//            $sqlExist = "select id from gr_sp_sellsummary where shopId=$shopId and timePeriod =$thisMonth";
//            $dtExist = $Model->query($sqlExist);
//            $sql = '';
//            if ($dtExist[0] . count() == '') {
//                $sql = "insert into gr_sp_sellsummary (shopId,shopName,timePeriod,currentMonthSell,currentDaySell,yesterdaySell,completionRate,createAt,updateAt) values ($shopId,'$shopName','$thisMonth',0,0,$total,0,$currentTime,$currentTime)";
//                $res = $Model->execute($sql);
//            } else {
//                $sql = "update gr_sp_sellsummary set yesterdaySell= $total,updateAt =$currentTime where shopId=$shopId and timePeriod ='$thisMonth' ";
//                $res = $Model->execute($sql);
//            }
//        }
//        //获取门店指标
//        $shopIndex = [];
//        $sql = "select * from gr_ec.gr_dict where dict_key='$thisMonth'";
//        $resIndex = $Model->query($sql);
//        for ($m = 0; $m < count($resIndex); $m++) {
//            $name = $resIndex[$m]["dict_group"];
//            if ($name == "京华城店销售指标") {
//                $shopIndexItem = [2, $resIndex[$m]["dict_value"]];
//            } else if ($name == "梅岭店销售指标") {
//                $shopIndexItem = [3, $resIndex[$m]["dict_value"]];
//            } else if ($name == "望月路店销售指标") {
//                $shopIndexItem = [4, $resIndex[$m]["dict_value"]];
//            } else if ($name == "三盛店销售指标") {
//                $shopIndexItem = [5, $resIndex[$m]["dict_value"]];
//            } else if ($name == "时代店销售指标") {
//                $shopIndexItem = [6, $resIndex[$m]["dict_value"]];
//            } else if ($name == "虹桥坊店销售指标") {
//                $shopIndexItem = [7, $resIndex[$m]["dict_value"]];
//            }
//            $shopIndex[] = $shopIndexItem;
//        }
//        for ($i = 0; $i < count($dtThisMonthSell); $i++) {
//            $shopId = $dtThisMonthSell[$i]["shopId"];
//            $shopName = $dtThisMonthSell[$i]["name"];
//            $total = $dtThisMonthSell[$i]["totalSell"];
//            $thisMonthRate = 0;
//            for ($m = 0; $m < count($shopIndex); $m++) {
//                if ("'" + $shopIndex[$m][0] + "'" == $shopId) {
//                    $thisMonthRate = (floatval($total) / floatval($shopIndex[$m][1])) * 100;
//                }
//            }
//            $sqlExist = "select id from gr_sp_sellsummary where shopId=$shopId and timePeriod =$thisMonth";
//            $dtExist = $Model->query($sqlExist);
//            $sql = '';
//            if ($dtExist[0] . count() == '') {
//                $sql = "insert into gr_sp_sellsummary (shopId,shopName,timePeriod,currentMonthSell,currentDaySell,yesterdaySell,completionRate,createAt,updateAt) values ($shopId,'$shopName','$thisMonth',$total,0,0,$thisMonthRate,$currentTime,$currentTime)";
//                $res = $Model->execute($sql);
//            } else {
//                $sql = "update gr_sp_sellsummary set currentMonthSell=$total,completionRate=$thisMonthRate,updateAt =$currentTime where shopId=$shopId and timePeriod ='$thisMonth' ";
//                $res = $Model->execute($sql);
//            }
//        }
//    }

    /**
     * 导出共用方法
     */
    public function getExcel($fileName, $headArr, $data, $title)
    {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            //die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }
        // H:i:s
        $date = date("Y_m_d_H_i_s", time());
        //$date = date("Y_m_d",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //第一列设置报表时间范围
        $key = ord("A");
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $title);

        //第二列设置报表列头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '2', $v);
            $key += 1;
        }

        $column = 3;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
//                $objActSheet->getColumnDimension('a')->setWidth(20);
                $objActSheet->getColumnDimension('b')->setWidth(50);
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }

    /**
     * 导出共用方法
     */
    public function getExcel_yue($fileName, $headArr, $data, $title)
    {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            //die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }
        // H:i:s
        $date = date("Y_m_d_H_i_s", time());
        //$date = date("Y_m_d",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //第一列设置报表时间范围
        $key = ord("A");
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $title);

        //第二列设置报表列头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '2', $v);
            $key += 1;
        }

        $column = 3;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
//                $objActSheet->getColumnDimension('a')->setWidth(20);
//                $objActSheet->getColumnDimension('b')->setWidth(50);
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }

    public function  membercostdaliy($overallnum = null){
        $Model = new Model();
        $sql = "select beginTime,endTime,shopId from gr_sp_overalldaliy where overallNum='$overallnum'";
        $temp = $Model->query($sql);
        if ($temp != null && count($temp) > 0) {
            $beginTime = $temp[0]["beginTime"];
            $endTime = $temp[0]["endTime"];
            $shopId = $temp[0]["shopId"];

            $sql="SELECT t.*,m.name,m.cardNum,g.firstPayTypeID,g.firstPayTypeAmount,g.secondPayTypeId,g.secondPayTypeAmount from (SELECT orderNum,memberId FROM `gr_sp_order` WHERE shopId=$shopId and createat>$beginTime and createat<$endTime and memberId>0 and type!=2) t INNER join `gr_sp_membercard` m on t.memberId=m.id INNER join `gr_sp_grouppay` g on t.orderNum=g.orderNum";
            $temp = $Model->query($sql);
            $res=[];
            $allAmount=0;
            for($i=0;$i<count($temp);$i++){
                if($temp[$i]['firstPayTypeID']==3){
                    $resItem=[$temp[$i]['name'],$temp[$i]['cardNum'],$temp[$i]['orderNum'],$temp[$i]['firstPayTypeAmount']];
                    $res[]=$resItem;
                    $allAmount+=floatval($temp[$i]['firstPayTypeAmount']);
                }else if($temp[$i]['secondPayTypeId']==3){
                    $resItem=[$temp[$i]['name'],$temp[$i]['cardNum'],$temp[$i]['orderNum'],$temp[$i]['secondPayTypeAmount']];
                    $res[]=$resItem;
                    $allAmount+=floatval($temp[$i]['secondPayTypeAmount']);
                }
            }
            $this->assign('_list',$res);
            $this->assign('allAmount',$allAmount);
        }
        $this->display();
    }
}
